
/**
	Author	     :  Rohan
	Date	     :  Sep 11th, 2004
 	Description  :  This proc returns the equivalant location in the GP database of the given
			packing slip line item.
	Usage        : 
			begin
			declare @gp_location char(11)
			exec sp_get_ps_line_item_gp_location 671, @gp_location out
			print @gp_location
			end
*/

ALTER   proc sp_get_ps_line_item_gp_location
(
	@CSLIT_ID numeric,
	@gp_location char(11) OUT
)
as
begin

declare @customer_shipping_id int
declare @vendor_name varchar(50)
declare @ps_type varchar(50)
declare @location_address_id int
declare @from_address_id int

set @gp_location = ''
set @vendor_name = ''
set @ps_type = ''

--select CustomerShipping_Id from Customer_Shipping_Line_Item_Table where CSLIT_ID = 671
select @customer_shipping_id = CustomerShipping_Id from Customer_Shipping_Line_Item_Table where CSLIT_ID = @CSLIT_ID

--Retreiving the vendor name
select @from_address_id = CST_From_Address_ID, @location_address_id = CST_Location_Address_ID from customer_shipping_table where CustomerShipping_ID = @customer_shipping_id

if( @location_address_id is null )
begin
	select @vendor_name = RTRIM( L_Vendor_Name ) from Location where Address_Id = @from_address_id and Location.L_Type = 'Finished Goods'
end
else
begin
	select @vendor_name = RTRIM( L_Vendor_Name ) from Location where Address_Id = @location_address_id and Location.L_Type = 'Finished Goods'
end

--Retreiving the process type
select @ps_type = RTRIM( PS.PS_Type_name ) from Customer_Shipping_Line_Item_Table CSLIT, Customer_Open_Order_LineItem COOL, Product PROD, Process_Step PS where CSLIT.LineItem_ID = COOL.LineItem_ID and COOL.Product_Id = PROD.Product_Id and PROD.ProcessStep_Id = PS.ProcessStep_Id and CSLIT.CSLIT_ID = @CSLIT_ID

if( len( @vendor_name ) > 7 )
begin
	set @vendor_name = substring( @vendor_name, 1, 7 )
end

if( len( @ps_type ) > 3 )
begin
	set @ps_type = substring( @ps_type, 1, 3 )
end

if( @vendor_name <> '' and @ps_type <> '' )
begin
	set @gp_location = upper( @vendor_name + '/' + @ps_type )
end

end --End of proc
